mysql8查看锁 |
您所在的位置:网站首页 › 查看mysql 用户 › mysql8查看锁 |
一。准备环境 1.登录mysql,设置锁等待时间,然后退出。 mysql> set global innodb_lock_wait_timeout=1200; Query OK, 0 rows affected (0.00 sec) 2.再次登录MySQL。打开第一个session 3.登录msyql,打开第二个session 2.登录打开第二个session mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) mysql> use test mysql> update temp set name='bb' where id=1; #执行这条sql,会一直卡住。4.登录MySQL,打开第三个session。 a:查看 mysql> select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked,trx_query from information_schema.innodb_trx;看上图trx_state的状态LOCK_WAIT,同时trx_query对应的sql语句,说明执行这条sql阻塞了。事务id的字段trx_id的值1755425 b:查看 mysql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;看上图的lock_status出现了一个WAITING,同时THREAD_ID为57的线程处于等待状态。对应的ENGINE_TRANSACTION_ID的值就是a操作的图字段trx_id的值1755425。 4.还要查看锁事务的对应关系, mysql> select locked_table_name,locked_index,waiting_pid,waiting_lock_id,blocking_lock_id,blocking_pid from sys.innodb_lock_waits;再次对比下。可以找到关系,1等待的事务处理,是被2阻塞了。也就是锁住了,需要2释放锁,1才可以执行 mysql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks; mysql> select locked_table_name,locked_index,waiting_pid,waiting_lock_id,blocking_lock_id,blocking_pid from sys.innodb_lock_waits;同时,查看waiting_pid跟blocking_pid的值就是线程id,对应processlist mysql> select locked_table_name,locked_index,waiting_pid,waiting_lock_id,blocking_lock_id,blocking_pid from sys.innodb_lock_waits; mysql> show processlist;5.现在上面图看不到blocking_pid对应的线程THREAD_PID mysql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks; mysql> select locked_table_name,locked_index,waiting_pid,waiting_lock_id,blocking_lock_id,blocking_pid from sys.innodb_lock_waits;6.通过上图可以看到。 blocking_pid为15 对应的线程THREAD_PID 为56. waiting_pid为16 对应的线程THREAD_PID 为57. mysql> select THREAD_ID,EVENT_ID,EVENT_NAME,SQL_TEXT from performance_schema.events_statements_history where THREAD_ID in (56,57) order by THREAD_ID,EVENT_ID;从图上分析,就可以看出blocking_pid,也就是已经锁了执行的sql是啥。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |